package DAO.ContrainteDAO;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DAO.DAO_Bd;
import Metier.Attribut;
import Metier.ContraintePk;

public abstract class ContrainteDAO_Bd implements ContrainteDAO {
	protected DAO_Bd dao;

	public List<String> getListeAttribut(String nom) throws SQLException {
		List<String> liste = new ArrayList<String>();
		DatabaseMetaData md = dao.getCx().getMetaData();
		ResultSet resultat = md.getColumns(null, null, nom, "%");

		while (resultat.next()) {
			liste.add(resultat.getString("COLUMN_NAME"));
		}
		resultat.close();
		return liste;
	}

	public void addForeignKey(String nomContrainte, String table1,
			String table2, List<String> attr1, List<String> attr2)
			throws SQLException {
		String requete = "ALTER TABLE " + table1 + " add CONSTRAINT "
				+ nomContrainte + " FOREIGN KEY (";
		for (int i = 0; i < attr1.size(); i++) {
			if (i != 0)
				requete += ",";
			requete += attr1.get(i);
		}
		requete += ")REFERENCES " + table2 + " (";
		for (int i = 0; i < attr2.size(); i++) {
			if (i != 0)
				requete += ",";
			requete += attr2.get(i);
		}
		requete += ")";
		dao.getSt().executeUpdate(requete);
	}

	public void deleteConstraint(String nomTable, String nomContrainte)
			throws SQLException {
		String requete = " ALTER TABLE " + nomTable + " drop CONSTRAINT "
				+ nomContrainte;
		dao.getSt().executeUpdate(requete);
	}

	public List<Attribut> getForeignKey(String nomTable) throws SQLException {
		List<Attribut> liste1 = new ArrayList<Attribut>();
		List<Attribut> liste2 = new ArrayList<Attribut>();
		DatabaseMetaData md = dao.getCx().getMetaData();
		ResultSet resultat = md.getExportedKeys(null, null, nomTable);
		while (resultat.next()) {
			Attribut att1 = new Attribut();
			Attribut att2 = new Attribut();
			att1.setTable(resultat.getString("PKTABLE_NAME"));
			att1.setNom(resultat.getString("PKCOLUMN_NAME"));
			att1.setForeignKey(resultat.getString("FK_NAME"));
			att2.setTable(resultat.getString("FKTABLE_NAME"));
			att2.setNom(resultat.getString("FKCOLUMN_NAME"));
			att2.setForeignKey(resultat.getString("FK_NAME"));
			liste1.add(att1);
			liste2.add(att2);
		}
		resultat.close();
		resultat = md.getImportedKeys(null, null, nomTable);
		while (resultat.next()) {
			Attribut att1 = new Attribut();
			Attribut att2 = new Attribut();
			att1.setTable(resultat.getString("PKTABLE_NAME"));
			att1.setNom(resultat.getString("PKCOLUMN_NAME"));
			att1.setForeignKey(resultat.getString("FK_NAME"));
			att2.setTable(resultat.getString("FKTABLE_NAME"));
			att2.setNom(resultat.getString("FKCOLUMN_NAME"));
			att2.setForeignKey(resultat.getString("FK_NAME"));
			liste1.add(att1);
			liste2.add(att2);
		}
		resultat.close();
		List<Attribut> liste = new ArrayList<Attribut>();
		for (int i = 0; i < liste1.size(); i++) {
			liste.add(liste1.get(i));
			liste.add(liste2.get(i));
		}
		return liste;
	}

	public int getIndex(String nomTable, Attribut attribut) throws SQLException {
		int index = -1;
		int i = 0;
		boolean stop = false;
		DatabaseMetaData md = dao.getCx().getMetaData();
		ResultSet resultat = md.getColumns(null, null, nomTable, "%");

		while (resultat.next() && !stop) {
			if (resultat.getString("COLUMN_NAME").equals(attribut.getNom())) {
				index = i;
				stop = true;
			}
			i++;
		}
		resultat.close();
		return index;
	}

	public void addPrimaryKey(String nomTable, List<String> listAttr)
			throws SQLException {
		String requete = "ALTER TABLE " + nomTable + " add CONSTRAINT pk_"
				+ nomTable + " PRIMARY KEY (";
		for (int i = 0; i < listAttr.size(); i++) {
			if (i != 0)
				requete += ",";
			requete += listAttr.get(i);
		}
		requete += ")";
		dao.getSt().executeUpdate(requete);
	}

	public ContraintePk getPrimaryKey(String nomTable) throws SQLException {
		List<String> listeAttributPk = new ArrayList<String>();
		String pK = "";
		DatabaseMetaData md = dao.getCx().getMetaData();
		ResultSet resultat = md.getPrimaryKeys(null, null, nomTable);
		while (resultat.next()) {
			listeAttributPk.add(resultat.getString("COLUMN_NAME"));
			pK = resultat.getString("PK_NAME");
		}
		resultat.close();
		return new ContraintePk(nomTable, pK, listeAttributPk);
	}

}
